set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


with city_slowest_route_tmp_table as (
   select
        in_from_code
       ,update_date
       ,in_from_provider_code
       ,in_from_city_code
       ,in_from_area_code
       ,out_to_provider_code
       ,out_to_city_code
       ,out_to_area_code
       ,out_to_code
       ,in_branch_id
       ,main_id
       ,out_branch_id
       ,in_collect_code
       ,in_to_code
       ,start_center_code
       ,search_type
       ,start_network_code
       ,in_from_regional_code
       ,in_from_regional_desc
       ,in_from_financial_center_code
       ,in_from_financial_center_desc
       ,in_from_provider_desc
       ,in_from_city_desc
       ,in_from_area_desc
       ,in_collect_name
       ,in_from_name
       ,in_to_name
       ,in_edge_latest_warehousing
       ,in_edge_planned_departure
       ,in_edge_planned_arrival
       ,in_edge2_planned_departure
       ,in_edge2_planned_arrival
       ,branch_in_span_days
       ,branch_in_total_time
       ,is_main_route
       ,transfer_type
       ,num_of_transfer
       ,concat_ws('-',in_from_name,in_collect_name,in_to_name,e1_end_name,e2_end_name,e3_end_name,e4_end_name,e5_end_name,e6_end_name,out_collect_name,out_to_name) as whole_route
       ,e1_center_flow
       ,e1_line_code
       ,e1_planned_departure
       ,e1_runtime
       ,e1_planned_arrival
       ,e1_e2_stop_time
       ,e2_center_flow
       ,e2_line_code
       ,e2_planned_departure
       ,e2_runtime
       ,e2_planned_arrival
       ,e2_e3_stop_time
       ,e3_center_flow
       ,e3_line_code
       ,e3_planned_departure
       ,e3_runtime
       ,e3_planned_arrival
       ,e3_e4_stop_time
       ,e4_center_flow
       ,e4_line_code
       ,e4_planned_departure
       ,e4_runtime
       ,e4_planned_arrival
       ,e4_e5_stop_time
       ,e5_center_flow
       ,e5_line_code
       ,e5_planned_departure
       ,e5_runtime
       ,e5_planned_arrival
       ,e5_e6_stop_time
       ,e6_center_flow
       ,e6_line_code
       ,e6_planned_departure
       ,e6_runtime
       ,e6_planned_arrival
       ,e6_e7_stop_time
       ,e1_end_code
       ,e2_end_code
       ,e3_end_code
       ,e4_end_code
       ,e5_end_code
       ,e6_end_code
       ,transfer_total_span_days
       ,transfer_total_time_use
       ,main_total_span_days
       ,main_total_time_use
       ,end_center
       ,end_center_code
       ,start_center
       ,out_from_code
       ,out_collect_code
       ,out_to_regional_desc
       ,out_to_regional_code
       ,out_to_financial_center_desc
       ,out_to_financial_center_code
       ,out_to_provider_desc
       ,out_to_city_desc
       ,out_to_area_desc
       ,out_collect_name
       ,out_to_name
       ,out_from_name
       ,out_has_collect
       ,out_edge_planned_departure
       ,out_edge_planned_arrival_time
       ,out_edge2_planned_departure
       ,out_edge2_planned_arrival_time
       ,out_edge2_latest_warehouse_time
       ,out_edge_latest_warehouse_time
       ,out_edge_span_days_sign
       ,out_edge2_span_days_sign
       ,out_edge_deadline_sign_time
       ,out_edge2_deadline_sign_time
       ,branch_out_span_days
       ,branch_out_total_time
       ,effective_date
       ,expiration_date
       ,branch_in_center_stop_time
       ,center_branch_out_stop_time
       ,branch_in_center_span_days
       ,center_branch_out_span_days
       ,is_circuitous
       ,out_edge_span_days_arrive
       ,out_edge2_span_days_arrive
       ,in_nodes
       ,out_nodes
       ,total_days_use
       ,total_time_use
       ,total_days_t
       ,total_nodes
       ,working_days
       ,extra_in_collect_code
       ,extra_in_collect_name
       ,extra_in_line_name
       ,extra_in_latest_warehousing
       ,extra_in_planned_departure
       ,extra_in_planned_arrival
       ,extra_out_collect_code
       ,extra_out_collect_name
       ,extra_out_line_name
       ,extra_out_latest_warehousing
       ,extra_out_planned_departure
       ,extra_out_planned_arrival
       ,in_network_line_name
       ,in_collect_line_name
       ,out_network_line_name
       ,out_collect_line_name
       ,town_plus_time
       ,branch_in_ship_time
       ,branch_in_ship_span_days
       ,branch_out_ship_time
       ,branch_out_ship_span_days
       ,all_line_name
       ,out_town_plus_time
       ,in_edge_run_time
       ,in_edge_span_days
       ,in_edge2_run_time
       ,in_edge2_span_days
       ,in_edge3_run_time
       ,in_edge3_span_days
       ,out_edge_run_time
       ,out_edge_span_days
       ,out_edge2_run_time
       ,out_edge2_span_days
       ,out_edge3_run_time
       ,out_edge3_span_days
       ,e1_span_days
       ,e2_span_days
       ,e3_span_days
       ,e4_span_days
       ,e5_span_days
       ,e6_span_days
       ,delivery_time
       ,route_flow
       ,in_edge2_latest_warehousing
       ,e1_latest_warehousing
       ,e2_latest_warehousing
       ,e3_latest_warehousing
       ,e4_latest_arrival_time
       ,e5_latest_arrival_time
       ,e6_latest_arrival_time
       ,start_taking_shift
       ,end_send_shift
       ,warehouse_end_time
       ,reserve_1
       ,reserve_2
       ,route_contain_main_line
       ,direct_route_type
       ,in_line_day
       ,out_line_day
       ,in_ship_span_days
       ,in_manage_region_code
       ,in_manage_region_name
       ,out_manage_region_code
       ,out_manage_region_name
       ,latest_warehousing_time_quantum as latest_warehousing_time_quantum --2021210新增揽收时间段
       ,dt
   from (
       select
            in_from_code
           ,update_date
           ,in_from_provider_code
           ,in_from_city_code
           ,in_from_area_code
           ,out_to_provider_code
           ,out_to_city_code
           ,out_to_area_code
           ,out_to_code
           ,in_branch_id
           ,main_id
           ,out_branch_id
           ,in_collect_code
           ,in_to_code
           ,start_center_code
           ,search_type
           ,start_network_code
           ,in_from_regional_code
           ,in_from_regional_desc
           ,in_from_financial_center_code
           ,in_from_financial_center_desc
           ,in_from_provider_desc
           ,in_from_city_desc
           ,in_from_area_desc
           ,if(length(in_collect_name)>1,in_collect_name,null) as in_collect_name
           ,if(length(in_from_name)>1,in_from_name,null) as in_from_name
           ,if(length(in_to_name)>1,in_to_name,null) as in_to_name
           ,in_edge_latest_warehousing
           ,in_edge_planned_departure
           ,in_edge_planned_arrival
           ,in_edge2_planned_departure
           ,in_edge2_planned_arrival
           ,branch_in_span_days
           ,branch_in_total_time
           ,is_main_route
           ,transfer_type
           ,num_of_transfer
           ,e1_center_flow
           ,e1_line_code
           ,e1_planned_departure
           ,e1_runtime
           ,e1_planned_arrival
           ,e1_e2_stop_time
           ,e2_center_flow
           ,e2_line_code
           ,e2_planned_departure
           ,e2_runtime
           ,e2_planned_arrival
           ,e2_e3_stop_time
           ,e3_center_flow
           ,e3_line_code
           ,e3_planned_departure
           ,e3_runtime
           ,e3_planned_arrival
           ,e3_e4_stop_time
           ,e4_center_flow
           ,e4_line_code
           ,e4_planned_departure
           ,e4_runtime
           ,e4_planned_arrival
           ,e4_e5_stop_time
           ,e5_center_flow
           ,e5_line_code
           ,e5_planned_departure
           ,e5_runtime
           ,e5_planned_arrival
           ,e5_e6_stop_time
           ,e6_center_flow
           ,e6_line_code
           ,e6_planned_departure
           ,e6_runtime
           ,e6_planned_arrival
           ,e6_e7_stop_time
           ,e1_end_code
           ,e2_end_code
           ,e3_end_code
           ,e4_end_code
           ,e5_end_code
           ,e6_end_code
           ,transfer_total_span_days
           ,transfer_total_time_use
           ,main_total_span_days
           ,main_total_time_use
           ,end_center
           ,end_center_code
           ,start_center
           ,out_from_code
           ,out_collect_code
           ,out_to_regional_desc
           ,out_to_regional_code
           ,out_to_financial_center_desc
           ,out_to_financial_center_code
           ,out_to_provider_desc
           ,out_to_city_desc
           ,out_to_area_desc
           ,if(length(out_collect_name)>1,out_collect_name,null) as out_collect_name
           ,if(length(out_to_name)>1,out_to_name,null) as out_to_name
           ,if(length(out_from_name)>1,out_from_name,null) as out_from_name
           ,out_has_collect
           ,out_edge_planned_departure
           ,out_edge_planned_arrival_time
           ,out_edge2_planned_departure
           ,out_edge2_planned_arrival_time
           ,out_edge2_latest_warehouse_time
           ,out_edge_latest_warehouse_time
           ,out_edge_span_days_sign
           ,out_edge2_span_days_sign
           ,out_edge_deadline_sign_time
           ,out_edge2_deadline_sign_time
           ,branch_out_span_days
           ,branch_out_total_time
           ,effective_date
           ,expiration_date
           ,branch_in_center_stop_time
           ,center_branch_out_stop_time
           ,branch_in_center_span_days
           ,center_branch_out_span_days
           ,is_circuitous
           ,out_edge_span_days_arrive
           ,out_edge2_span_days_arrive
           ,in_nodes
           ,out_nodes
           ,total_days_use
           ,total_time_use
           ,total_days_t
           ,total_nodes
           ,working_days
           ,extra_in_collect_code
           ,if(length(extra_in_collect_name)>1,extra_in_collect_name,null) as extra_in_collect_name
           ,extra_in_line_name
           ,extra_in_latest_warehousing
           ,extra_in_planned_departure
           ,extra_in_planned_arrival
           ,extra_out_collect_code
           ,if(length(extra_out_collect_name)>1,extra_out_collect_name,null) as extra_out_collect_name
           ,extra_out_line_name
           ,extra_out_latest_warehousing
           ,extra_out_planned_departure
           ,extra_out_planned_arrival
           ,in_network_line_name
           ,in_collect_line_name
           ,out_network_line_name
           ,out_collect_line_name
           ,town_plus_time
           ,branch_in_ship_time
           ,branch_in_ship_span_days
           ,branch_out_ship_time
           ,branch_out_ship_span_days
           ,all_line_name
           ,out_town_plus_time
           ,in_edge_run_time
           ,in_edge_span_days
           ,in_edge2_run_time
           ,in_edge2_span_days
           ,in_edge3_run_time
           ,in_edge3_span_days
           ,out_edge_run_time
           ,out_edge_span_days
           ,out_edge2_run_time
           ,out_edge2_span_days
           ,out_edge3_run_time
           ,out_edge3_span_days
           ,e1_span_days
           ,e2_span_days
           ,e3_span_days
           ,e4_span_days
           ,e5_span_days
           ,e6_span_days
           ,delivery_time
           ,route_flow
           ,in_edge2_latest_warehousing
           ,e1_latest_warehousing
           ,e2_latest_warehousing
           ,e3_latest_warehousing
           ,e4_latest_arrival_time
           ,e5_latest_arrival_time
           ,e6_latest_arrival_time
           ,start_taking_shift
           ,end_send_shift
           ,warehouse_end_time
           ,reserve_1
           ,reserve_2
           ,route_contain_main_line
           ,direct_route_type
           ,in_line_day
           ,out_line_day
           ,in_ship_span_days
           ,in_manage_region_code
           ,in_manage_region_name
           ,out_manage_region_code
           ,out_manage_region_name
           ,latest_warehousing_time_quantum as latest_warehousing_time_quantum --2021210新增揽收时间段
           ,network_detail_1.name as e1_end_name
           ,network_detail_2.name as e2_end_name
           ,network_detail_3.name as e3_end_name
           ,network_detail_4.name as e4_end_name
           ,network_detail_5.name as e5_end_name
           ,network_detail_6.name as e6_end_name
           ,dt
           ,row_number() over(partition by in_from_area_code,out_to_area_code order by search_type desc,total_time_use desc,in_edge_planned_departure desc,in_from_code desc,out_to_code desc,in_from_area_code desc,out_to_area_code desc,start_center_code desc,end_center_code desc) as rn
       from (
           select *
           from jms_dm.dm_route_slowest --最慢路由
           where dt = '{{ execution_date | cst_ds }}'
       ) route_fastest
       left join (
           select code,name
           from jms_dim.dim_network_whole_massage
       ) network_detail_1 on network_detail_1.code = route_fastest.e1_end_code
       left join (
           select code,name
           from jms_dim.dim_network_whole_massage
       ) network_detail_2 on network_detail_2.code = route_fastest.e2_end_code
       left join (
           select code,name
           from jms_dim.dim_network_whole_massage
       ) network_detail_3 on network_detail_3.code = route_fastest.e3_end_code
       left join (
           select code,name
           from jms_dim.dim_network_whole_massage
       ) network_detail_4 on network_detail_4.code = route_fastest.e4_end_code
       left join (
           select code,name
           from jms_dim.dim_network_whole_massage
       ) network_detail_5 on network_detail_5.code = route_fastest.e5_end_code
       left join (
           select code,name
           from jms_dim.dim_network_whole_massage
       ) network_detail_6 on network_detail_6.code = route_fastest.e6_end_code
   ) a where a.rn = 1
),




dim_yl_tms_bulk_cargo_shift_base as (
    select
         id               --ID
        ,region_name      --大区
        ,region_code      --大区编号
        ,province_name    --省份
        ,province_code    --省份编号
        ,agent_name       --代理区
        ,agent_code       --代理区编号
        ,center_name      --规划转运中心
        ,center_code      --规划转运中心编号
        ,city_name        --城市
        ,city_code        --城市编号
        ,area_name        --区县
        ,area_code        --区县编号
    from jms_dim.dim_yl_tms_bulk_cargo_shift_base   --散货班次表
),


city_slowest_route_result_tmp as (
    select
        city_slowest_route.in_from_code as in_from_code
       ,date_add('{{ execution_date | cst_ds }}',1) as update_date
       ,nvl(city_slowest_route.in_from_provider_code,shift_start.province_code) as in_from_provider_code
       ,route_area.city_id_start as in_from_city_code
       ,route_area.area_id_start as in_from_area_code
       ,nvl(city_slowest_route.out_to_provider_code,shift_end.province_code) as out_to_provider_code
       ,route_area.city_id_end as out_to_city_code
       ,route_area.area_id_end as out_to_area_code
       ,city_slowest_route.out_to_code as out_to_code
       ,city_slowest_route.in_branch_id as in_branch_id
       ,city_slowest_route.main_id as main_id
       ,city_slowest_route.out_branch_id as out_branch_id
       ,city_slowest_route.in_collect_code as in_collect_code
       ,nvl(city_slowest_route.in_to_code,shift_start.center_code) as in_to_code
       ,nvl(city_slowest_route.start_center_code,shift_start.center_code) as start_center_code
       ,city_slowest_route.search_type as search_type
       ,city_slowest_route.start_network_code as start_network_code
       ,nvl(city_slowest_route.in_from_regional_code,shift_start.region_code) as in_from_regional_code
       ,nvl(city_slowest_route.in_from_regional_desc,shift_start.region_name) as in_from_regional_desc
       ,nvl(city_slowest_route.in_from_financial_center_code,shift_start.agent_code) as in_from_financial_center_code
       ,nvl(city_slowest_route.in_from_financial_center_desc,shift_start.agent_name) as in_from_financial_center_desc
       ,nvl(city_slowest_route.in_from_provider_desc,shift_start.province_name) as in_from_provider_desc
       ,route_area.city_start as in_from_city_desc
       ,route_area.area_start as in_from_area_desc
       ,city_slowest_route.in_collect_name as in_collect_name
       ,city_slowest_route.in_from_name as in_from_name
       ,nvl(city_slowest_route.in_to_name,shift_start.center_name)as in_to_name
       ,city_slowest_route.in_edge_latest_warehousing as in_edge_latest_warehousing
       ,city_slowest_route.in_edge_planned_departure as in_edge_planned_departure
       ,city_slowest_route.in_edge_planned_arrival as in_edge_planned_arrival
       ,city_slowest_route.in_edge2_planned_departure as in_edge2_planned_departure
       ,city_slowest_route.in_edge2_planned_arrival as in_edge2_planned_arrival
       ,city_slowest_route.branch_in_span_days as branch_in_span_days
       ,city_slowest_route.branch_in_total_time as branch_in_total_time
       ,city_slowest_route.is_main_route as is_main_route
       ,city_slowest_route.transfer_type as transfer_type
       ,city_slowest_route.num_of_transfer as num_of_transfer
       ,city_slowest_route.whole_route as whole_route
       ,city_slowest_route.e1_center_flow as e1_center_flow
       ,city_slowest_route.e1_line_code as e1_line_code
       ,city_slowest_route.e1_planned_departure as e1_planned_departure
       ,city_slowest_route.e1_runtime as e1_runtime
       ,city_slowest_route.e1_planned_arrival as e1_planned_arrival
       ,city_slowest_route.e1_e2_stop_time as e1_e2_stop_time
       ,city_slowest_route.e2_center_flow as e2_center_flow
       ,city_slowest_route.e2_line_code as e2_line_code
       ,city_slowest_route.e2_planned_departure as e2_planned_departure
       ,city_slowest_route.e2_runtime as e2_runtime
       ,city_slowest_route.e2_planned_arrival as e2_planned_arrival
       ,city_slowest_route.e2_e3_stop_time as e2_e3_stop_time
       ,city_slowest_route.e3_center_flow as e3_center_flow
       ,city_slowest_route.e3_line_code as e3_line_code
       ,city_slowest_route.e3_planned_departure as e3_planned_departure
       ,city_slowest_route.e3_runtime as e3_runtime
       ,city_slowest_route.e3_planned_arrival as e3_planned_arrival
       ,city_slowest_route.e3_e4_stop_time as e3_e4_stop_time
       ,city_slowest_route.e4_center_flow as e4_center_flow
       ,city_slowest_route.e4_line_code as e4_line_code
       ,city_slowest_route.e4_planned_departure as e4_planned_departure
       ,city_slowest_route.e4_runtime as e4_runtime
       ,city_slowest_route.e4_planned_arrival as e4_planned_arrival
       ,city_slowest_route.e4_e5_stop_time as e4_e5_stop_time
       ,city_slowest_route.e5_center_flow as e5_center_flow
       ,city_slowest_route.e5_line_code as e5_line_code
       ,city_slowest_route.e5_planned_departure as e5_planned_departure
       ,city_slowest_route.e5_runtime as e5_runtime
       ,city_slowest_route.e5_planned_arrival as e5_planned_arrival
       ,city_slowest_route.e5_e6_stop_time as e5_e6_stop_time
       ,city_slowest_route.e6_center_flow as e6_center_flow
       ,city_slowest_route.e6_line_code as e6_line_code
       ,city_slowest_route.e6_planned_departure as e6_planned_departure
       ,city_slowest_route.e6_runtime as e6_runtime
       ,city_slowest_route.e6_planned_arrival as e6_planned_arrival
       ,city_slowest_route.e6_e7_stop_time as e6_e7_stop_time
       ,city_slowest_route.e1_end_code as e1_end_code
       ,city_slowest_route.e2_end_code as e2_end_code
       ,city_slowest_route.e3_end_code as e3_end_code
       ,city_slowest_route.e4_end_code as e4_end_code
       ,city_slowest_route.e5_end_code as e5_end_code
       ,city_slowest_route.e6_end_code as e6_end_code
       ,city_slowest_route.transfer_total_span_days as transfer_total_span_days
       ,city_slowest_route.transfer_total_time_use as transfer_total_time_use
       ,city_slowest_route.main_total_span_days as main_total_span_days
       ,city_slowest_route.main_total_time_use as main_total_time_use
       ,nvl(city_slowest_route.end_center,shift_end.center_name) as end_center
       ,nvl(city_slowest_route.end_center_code,shift_end.center_code) as end_center_code
       ,nvl(city_slowest_route.start_center,shift_start.center_name) as start_center
       ,nvl(city_slowest_route.out_from_code,shift_end.center_code) as out_from_code
       ,city_slowest_route.out_collect_code as out_collect_code
       ,nvl(city_slowest_route.out_to_regional_desc,shift_end.region_name) as out_to_regional_desc
       ,nvl(city_slowest_route.out_to_regional_code,shift_end.region_code) as out_to_regional_code
       ,nvl(city_slowest_route.out_to_financial_center_desc,shift_end.agent_name) as out_to_financial_center_desc
       ,nvl(city_slowest_route.out_to_financial_center_code,shift_end.agent_code) as out_to_financial_center_code
       ,nvl(city_slowest_route.out_to_provider_desc,shift_end.province_name) as out_to_provider_desc
       ,route_area.city_end as out_to_city_desc
       ,route_area.area_end as out_to_area_desc
       ,city_slowest_route.out_collect_name as out_collect_name
       ,city_slowest_route.out_to_name as out_to_name
       ,nvl(city_slowest_route.out_from_name,shift_end.center_name) as out_from_name
       ,city_slowest_route.out_has_collect as out_has_collect
       ,city_slowest_route.out_edge_planned_departure as out_edge_planned_departure
       ,city_slowest_route.out_edge_planned_arrival_time as out_edge_planned_arrival_time
       ,city_slowest_route.out_edge2_planned_departure as out_edge2_planned_departure
       ,city_slowest_route.out_edge2_planned_arrival_time as out_edge2_planned_arrival_time
       ,city_slowest_route.out_edge2_latest_warehouse_time as out_edge2_latest_warehouse_time
       ,city_slowest_route.out_edge_latest_warehouse_time as out_edge_latest_warehouse_time
       ,city_slowest_route.out_edge_span_days_sign as out_edge_span_days_sign
       ,city_slowest_route.out_edge2_span_days_sign as out_edge2_span_days_sign
       ,city_slowest_route.out_edge_deadline_sign_time as out_edge_deadline_sign_time
       ,city_slowest_route.out_edge2_deadline_sign_time as out_edge2_deadline_sign_time
       ,city_slowest_route.branch_out_span_days as branch_out_span_days
       ,city_slowest_route.branch_out_total_time as branch_out_total_time
       ,city_slowest_route.effective_date as effective_date
       ,city_slowest_route.expiration_date as expiration_date
       ,city_slowest_route.branch_in_center_stop_time as branch_in_center_stop_time
       ,city_slowest_route.center_branch_out_stop_time as center_branch_out_stop_time
       ,city_slowest_route.branch_in_center_span_days as branch_in_center_span_days
       ,city_slowest_route.center_branch_out_span_days as center_branch_out_span_days
       ,city_slowest_route.is_circuitous as is_circuitous
       ,city_slowest_route.out_edge_span_days_arrive as out_edge_span_days_arrive
       ,city_slowest_route.out_edge2_span_days_arrive as out_edge2_span_days_arrive
       ,city_slowest_route.in_nodes as in_nodes
       ,city_slowest_route.out_nodes as out_nodes
       ,city_slowest_route.total_days_use as total_days_use
       ,city_slowest_route.total_time_use as total_time_use
       ,city_slowest_route.total_days_t as total_days_t
       ,city_slowest_route.total_nodes as total_nodes
       ,city_slowest_route.working_days as working_days
       ,city_slowest_route.extra_in_collect_code as extra_in_collect_code
       ,city_slowest_route.extra_in_collect_name as extra_in_collect_name
       ,city_slowest_route.extra_in_line_name as extra_in_line_name
       ,city_slowest_route.extra_in_latest_warehousing as extra_in_latest_warehousing
       ,city_slowest_route.extra_in_planned_departure as extra_in_planned_departure
       ,city_slowest_route.extra_in_planned_arrival as extra_in_planned_arrival
       ,city_slowest_route.extra_out_collect_code as extra_out_collect_code
       ,city_slowest_route.extra_out_collect_name as extra_out_collect_name
       ,city_slowest_route.extra_out_line_name as extra_out_line_name
       ,city_slowest_route.extra_out_latest_warehousing as extra_out_latest_warehousing
       ,city_slowest_route.extra_out_planned_departure as extra_out_planned_departure
       ,city_slowest_route.extra_out_planned_arrival as extra_out_planned_arrival
       ,city_slowest_route.in_network_line_name as in_network_line_name
       ,city_slowest_route.in_collect_line_name as in_collect_line_name
       ,city_slowest_route.out_network_line_name as out_network_line_name
       ,city_slowest_route.out_collect_line_name as out_collect_line_name
       ,city_slowest_route.town_plus_time as town_plus_time
       ,city_slowest_route.branch_in_ship_time as branch_in_ship_time
       ,city_slowest_route.branch_in_ship_span_days as branch_in_ship_span_days
       ,city_slowest_route.branch_out_ship_time as branch_out_ship_time
       ,city_slowest_route.branch_out_ship_span_days as branch_out_ship_span_days
       ,city_slowest_route.all_line_name as all_line_name
       ,city_slowest_route.out_town_plus_time as out_town_plus_time
       ,city_slowest_route.in_edge_run_time as in_edge_run_time
       ,city_slowest_route.in_edge_span_days as in_edge_span_days
       ,city_slowest_route.in_edge2_run_time as in_edge2_run_time
       ,city_slowest_route.in_edge2_span_days as in_edge2_span_days
       ,city_slowest_route.in_edge3_run_time as in_edge3_run_time
       ,city_slowest_route.in_edge3_span_days as in_edge3_span_days
       ,city_slowest_route.out_edge_run_time as out_edge_run_time
       ,city_slowest_route.out_edge_span_days as out_edge_span_days
       ,city_slowest_route.out_edge2_run_time as out_edge2_run_time
       ,city_slowest_route.out_edge2_span_days as out_edge2_span_days
       ,city_slowest_route.out_edge3_run_time as out_edge3_run_time
       ,city_slowest_route.out_edge3_span_days as out_edge3_span_days
       ,city_slowest_route.e1_span_days as e1_span_days
       ,city_slowest_route.e2_span_days as e2_span_days
       ,city_slowest_route.e3_span_days as e3_span_days
       ,city_slowest_route.e4_span_days as e4_span_days
       ,city_slowest_route.e5_span_days as e5_span_days
       ,city_slowest_route.e6_span_days as e6_span_days
       ,city_slowest_route.delivery_time as delivery_time
       ,city_slowest_route.route_flow as route_flow
       ,city_slowest_route.in_edge2_latest_warehousing as in_edge2_latest_warehousing
       ,city_slowest_route.e1_latest_warehousing as e1_latest_warehousing
       ,city_slowest_route.e2_latest_warehousing as e2_latest_warehousing
       ,city_slowest_route.e3_latest_warehousing as e3_latest_warehousing
       ,city_slowest_route.e4_latest_arrival_time as e4_latest_arrival_time
       ,city_slowest_route.e5_latest_arrival_time as e5_latest_arrival_time
       ,city_slowest_route.e6_latest_arrival_time as e6_latest_arrival_time
       ,city_slowest_route.start_taking_shift as start_taking_shift
       ,city_slowest_route.end_send_shift as end_send_shift
       ,city_slowest_route.warehouse_end_time as warehouse_end_time
       ,city_slowest_route.reserve_1 as reserve_1
       ,city_slowest_route.reserve_2 as reserve_2
       ,city_slowest_route.route_contain_main_line as route_contain_main_line
       ,city_slowest_route.direct_route_type as direct_route_type
       ,city_slowest_route.in_line_day as in_line_day
       ,city_slowest_route.out_line_day as out_line_day
       ,city_slowest_route.in_ship_span_days as in_ship_span_days
       ,city_slowest_route.in_manage_region_code as in_manage_region_code
       ,city_slowest_route.in_manage_region_name as in_manage_region_name
       ,city_slowest_route.out_manage_region_code as out_manage_region_code
       ,city_slowest_route.out_manage_region_name as out_manage_region_name
       ,if(route_main_city.area_id is not null and route_main_city_end.area_id is not null,1,0) as is_main_city
       ,route_area.config_area_id_start as config_area_id_start
       ,route_area.config_area_id_end as config_area_id_end
   from (
       select
            route_full_area.city_id              as city_id_start
           ,route_full_area.area_id             as area_id_start
           ,route_full_area.area                as area_start
           ,route_full_area_end.area_id         as area_id_end
           ,route_full_area_end.area            as area_end
           ,route_full_area_end.city_id         as city_id_end
           ,route_full_area.city                as city_start
           ,route_full_area_end.city            as city_end
           ,route_full_area.aging_area_code     as config_area_id_start
           ,route_full_area_end.aging_area_code as config_area_id_end
       from (
           select
                city_id
               ,city
               ,area_id
               ,area,aging_city_code
               ,aging_city_desc
               ,aging_area_code
               ,aging_area_desc
           from jms_dim.dim_tab_rou_main_city
           where is_enable=1
       ) route_full_area
       cross join (
           select
                city_id
                ,city
                ,area_id
                ,area
                ,aging_city_code
                ,aging_city_desc
                ,aging_area_code
                ,aging_area_desc
           from jms_dim.dim_tab_rou_main_city
           where is_enable=1
       ) route_full_area_end
   ) route_area
   left join city_slowest_route_tmp_table city_slowest_route
      on city_slowest_route.in_from_area_code=route_area.area_id_start
     and city_slowest_route.out_to_area_code=route_area.area_id_end
   left join (
       select *
       from jms_dim.dim_tab_rou_main_city
       where is_enable=1
       and is_main=1
   ) route_main_city on route_main_city.area_id=route_area.area_id_start
   left join(
       select *
       from jms_dim.dim_tab_rou_main_city
       where is_enable=1
       and is_main=1
   ) route_main_city_end on route_main_city_end.area_id=route_area.area_id_end
   left join dim_yl_tms_bulk_cargo_shift_base shift_start --关联散货班次，拿出基础资料
     on shift_start.area_code = route_area.area_id_start
   left join dim_yl_tms_bulk_cargo_shift_base shift_end
     on shift_end.area_code = route_area.area_id_end
),

city_slowest_route_result_config as (
    select
          city_slowest_route_start.in_from_code
         ,date_add('{{ execution_date | cst_ds }}',1) as update_date
         ,nvl(city_slowest_route_start.in_from_provider_code,shift_start.province_code) as in_from_provider_code
         ,city_slowest_route.in_from_city_code
         ,city_slowest_route.in_from_area_code
         ,nvl(city_slowest_route_start.out_to_provider_code,shift_end.province_code) as out_to_provider_code
         ,city_slowest_route.out_to_city_code
         ,city_slowest_route.out_to_area_code
         ,city_slowest_route_start.out_to_code
         ,city_slowest_route_start.in_branch_id
         ,city_slowest_route_start.main_id
         ,city_slowest_route_start.out_branch_id
         ,city_slowest_route_start.in_collect_code
         ,nvl(city_slowest_route_start.in_to_code,shift_start.center_code) as in_to_code
         ,nvl(city_slowest_route_start.start_center_code,shift_start.center_code) as start_center_code
         ,city_slowest_route_start.search_type
         ,city_slowest_route_start.start_network_code
         ,nvl(city_slowest_route_start.in_from_regional_code,shift_start.region_code) as in_from_regional_code
         ,nvl(city_slowest_route_start.in_from_regional_desc,shift_start.region_name) as in_from_regional_desc
         ,nvl(city_slowest_route_start.in_from_financial_center_code,shift_start.agent_code) as in_from_financial_center_code
         ,nvl(city_slowest_route_start.in_from_financial_center_desc,shift_start.agent_name) as in_from_financial_center_desc
         ,nvl(city_slowest_route_start.in_from_provider_desc,shift_start.province_name) as in_from_provider_desc
         ,city_slowest_route.in_from_city_desc
         ,city_slowest_route.in_from_area_desc
         ,city_slowest_route_start.in_collect_name
         ,city_slowest_route_start.in_from_name
         ,nvl(city_slowest_route_start.in_to_name,shift_start.center_name) as in_to_name
         ,city_slowest_route_start.in_edge_latest_warehousing
         ,city_slowest_route_start.in_edge_planned_departure
         ,city_slowest_route_start.in_edge_planned_arrival
         ,city_slowest_route_start.in_edge2_planned_departure
         ,city_slowest_route_start.in_edge2_planned_arrival
         ,city_slowest_route_start.branch_in_span_days
         ,city_slowest_route_start.branch_in_total_time
         ,city_slowest_route_start.is_main_route
         ,city_slowest_route_start.transfer_type
         ,city_slowest_route_start.num_of_transfer
         ,city_slowest_route_start.whole_route
         ,city_slowest_route_start.e1_center_flow
         ,city_slowest_route_start.e1_line_code
         ,city_slowest_route_start.e1_planned_departure
         ,city_slowest_route_start.e1_runtime
         ,city_slowest_route_start.e1_planned_arrival
         ,city_slowest_route_start.e1_e2_stop_time
         ,city_slowest_route_start.e2_center_flow
         ,city_slowest_route_start.e2_line_code
         ,city_slowest_route_start.e2_planned_departure
         ,city_slowest_route_start.e2_runtime
         ,city_slowest_route_start.e2_planned_arrival
         ,city_slowest_route_start.e2_e3_stop_time
         ,city_slowest_route_start.e3_center_flow
         ,city_slowest_route_start.e3_line_code
         ,city_slowest_route_start.e3_planned_departure
         ,city_slowest_route_start.e3_runtime
         ,city_slowest_route_start.e3_planned_arrival
         ,city_slowest_route_start.e3_e4_stop_time
         ,city_slowest_route_start.e4_center_flow
         ,city_slowest_route_start.e4_line_code
         ,city_slowest_route_start.e4_planned_departure
         ,city_slowest_route_start.e4_runtime
         ,city_slowest_route_start.e4_planned_arrival
         ,city_slowest_route_start.e4_e5_stop_time
         ,city_slowest_route_start.e5_center_flow
         ,city_slowest_route_start.e5_line_code
         ,city_slowest_route_start.e5_planned_departure
         ,city_slowest_route_start.e5_runtime
         ,city_slowest_route_start.e5_planned_arrival
         ,city_slowest_route_start.e5_e6_stop_time
         ,city_slowest_route_start.e6_center_flow
         ,city_slowest_route_start.e6_line_code
         ,city_slowest_route_start.e6_planned_departure
         ,city_slowest_route_start.e6_runtime
         ,city_slowest_route_start.e6_planned_arrival
         ,city_slowest_route_start.e6_e7_stop_time
         ,city_slowest_route_start.e1_end_code
         ,city_slowest_route_start.e2_end_code
         ,city_slowest_route_start.e3_end_code
         ,city_slowest_route_start.e4_end_code
         ,city_slowest_route_start.e5_end_code
         ,city_slowest_route_start.e6_end_code
         ,city_slowest_route_start.transfer_total_span_days
         ,city_slowest_route_start.transfer_total_time_use
         ,city_slowest_route_start.main_total_span_days
         ,city_slowest_route_start.main_total_time_use
         ,nvl(city_slowest_route_start.end_center,shift_end.center_name) as end_center
         ,nvl(city_slowest_route_start.end_center_code,shift_end.center_code) as end_center_code
         ,nvl(city_slowest_route_start.start_center,shift_start.center_name) as start_center
         ,nvl(city_slowest_route_start.out_from_code,shift_end.center_code) as out_from_code
         ,city_slowest_route_start.out_collect_code as out_collect_code
         ,nvl(city_slowest_route_start.out_to_regional_desc,shift_end.region_name) as out_to_regional_desc
         ,nvl(city_slowest_route_start.out_to_regional_code,shift_end.region_code) as out_to_regional_code
         ,nvl(city_slowest_route_start.out_to_financial_center_desc,shift_end.agent_name) as out_to_financial_center_desc
         ,nvl(city_slowest_route_start.out_to_financial_center_code,shift_end.agent_code) as out_to_financial_center_code
         ,nvl(city_slowest_route_start.out_to_provider_desc,shift_end.province_name) as out_to_provider_desc
         ,city_slowest_route.out_to_city_desc
         ,city_slowest_route.out_to_area_desc
         ,city_slowest_route_start.out_collect_name
         ,city_slowest_route_start.out_to_name
         ,nvl(city_slowest_route_start.out_from_name,shift_end.center_name) as out_from_name
         ,city_slowest_route_start.out_has_collect
         ,city_slowest_route_start.out_edge_planned_departure
         ,city_slowest_route_start.out_edge_planned_arrival_time
         ,city_slowest_route_start.out_edge2_planned_departure
         ,city_slowest_route_start.out_edge2_planned_arrival_time
         ,city_slowest_route_start.out_edge2_latest_warehouse_time
         ,city_slowest_route_start.out_edge_latest_warehouse_time
         ,city_slowest_route_start.out_edge_span_days_sign
         ,city_slowest_route_start.out_edge2_span_days_sign
         ,city_slowest_route_start.out_edge_deadline_sign_time
         ,city_slowest_route_start.out_edge2_deadline_sign_time
         ,city_slowest_route_start.branch_out_span_days
         ,city_slowest_route_start.branch_out_total_time
         ,city_slowest_route_start.effective_date
         ,city_slowest_route_start.expiration_date
         ,city_slowest_route_start.branch_in_center_stop_time
         ,city_slowest_route_start.center_branch_out_stop_time
         ,city_slowest_route_start.branch_in_center_span_days
         ,city_slowest_route_start.center_branch_out_span_days
         ,city_slowest_route_start.is_circuitous
         ,city_slowest_route_start.out_edge_span_days_arrive
         ,city_slowest_route_start.out_edge2_span_days_arrive
         ,city_slowest_route_start.in_nodes
         ,city_slowest_route_start.out_nodes
         ,city_slowest_route_start.total_days_use
         ,city_slowest_route_start.total_time_use
         ,city_slowest_route_start.total_days_t
         ,city_slowest_route_start.total_nodes
         ,city_slowest_route_start.working_days
         ,city_slowest_route_start.extra_in_collect_code
         ,city_slowest_route_start.extra_in_collect_name
         ,city_slowest_route_start.extra_in_line_name
         ,city_slowest_route_start.extra_in_latest_warehousing
         ,city_slowest_route_start.extra_in_planned_departure
         ,city_slowest_route_start.extra_in_planned_arrival
         ,city_slowest_route_start.extra_out_collect_code
         ,city_slowest_route_start.extra_out_collect_name
         ,city_slowest_route_start.extra_out_line_name
         ,city_slowest_route_start.extra_out_latest_warehousing
         ,city_slowest_route_start.extra_out_planned_departure
         ,city_slowest_route_start.extra_out_planned_arrival
         ,city_slowest_route_start.in_network_line_name
         ,city_slowest_route_start.in_collect_line_name
         ,city_slowest_route_start.out_network_line_name
         ,city_slowest_route_start.out_collect_line_name
         ,city_slowest_route_start.town_plus_time
         ,city_slowest_route_start.branch_in_ship_time
         ,city_slowest_route_start.branch_in_ship_span_days
         ,city_slowest_route_start.branch_out_ship_time
         ,city_slowest_route_start.branch_out_ship_span_days
         ,city_slowest_route_start.all_line_name
         ,city_slowest_route_start.out_town_plus_time
         ,city_slowest_route_start.in_edge_run_time
         ,city_slowest_route_start.in_edge_span_days
         ,city_slowest_route_start.in_edge2_run_time
         ,city_slowest_route_start.in_edge2_span_days
         ,city_slowest_route_start.in_edge3_run_time
         ,city_slowest_route_start.in_edge3_span_days
         ,city_slowest_route_start.out_edge_run_time
         ,city_slowest_route_start.out_edge_span_days
         ,city_slowest_route_start.out_edge2_run_time
         ,city_slowest_route_start.out_edge2_span_days
         ,city_slowest_route_start.out_edge3_run_time
         ,city_slowest_route_start.out_edge3_span_days
         ,city_slowest_route_start.e1_span_days
         ,city_slowest_route_start.e2_span_days
         ,city_slowest_route_start.e3_span_days
         ,city_slowest_route_start.e4_span_days
         ,city_slowest_route_start.e5_span_days
         ,city_slowest_route_start.e6_span_days
         ,city_slowest_route_start.delivery_time
         ,city_slowest_route_start.route_flow
         ,city_slowest_route_start.in_edge2_latest_warehousing
         ,city_slowest_route_start.e1_latest_warehousing
         ,city_slowest_route_start.e2_latest_warehousing
         ,city_slowest_route_start.e3_latest_warehousing
         ,city_slowest_route_start.e4_latest_arrival_time
         ,city_slowest_route_start.e5_latest_arrival_time
         ,city_slowest_route_start.e6_latest_arrival_time
         ,city_slowest_route_start.start_taking_shift
         ,city_slowest_route_start.end_send_shift
         ,city_slowest_route_start.warehouse_end_time
         ,city_slowest_route_start.reserve_1
         ,city_slowest_route_start.reserve_2
         ,city_slowest_route_start.route_contain_main_line
         ,city_slowest_route_start.direct_route_type
         ,city_slowest_route_start.in_line_day
         ,city_slowest_route_start.out_line_day
         ,city_slowest_route_start.in_ship_span_days
         ,city_slowest_route_start.in_manage_region_code
         ,city_slowest_route_start.in_manage_region_name
         ,city_slowest_route_start.out_manage_region_code
         ,city_slowest_route_start.out_manage_region_name
         ,if(route_main_city.area_id is not null and route_main_city_end.area_id is not null,1,0) as is_main_city
     from (
         select *
         from city_slowest_route_result_tmp
         where config_area_id_start is not null or config_area_id_end is not null
     ) city_slowest_route
     left join city_slowest_route_tmp_table city_slowest_route_start
        on city_slowest_route_start.in_from_area_code=coalesce(city_slowest_route.config_area_id_start,city_slowest_route.in_from_area_code)
       and city_slowest_route_start.out_to_area_code=coalesce(city_slowest_route.config_area_id_end,city_slowest_route.out_to_area_code)
     left join(
         select *
         from jms_dim.dim_tab_rou_main_city
         where is_enable=1
         and is_main=1
     ) route_main_city on route_main_city.area_id=city_slowest_route.in_from_area_code
     left join (
         select *
         from jms_dim.dim_tab_rou_main_city
         where is_enable=1
         and is_main=1
     ) route_main_city_end on route_main_city_end.area_id=city_slowest_route.out_to_area_code
     left join dim_yl_tms_bulk_cargo_shift_base shift_start --关联散货班次，拿出基础资料
        on shift_start.area_code =city_slowest_route.in_from_area_code
     left join dim_yl_tms_bulk_cargo_shift_base shift_end
        on shift_end.area_code =city_slowest_route.out_to_area_code
)



insert overwrite table jms_dm.dm_route_city_slowest_dt partition(dt = '{{ execution_date | cst_ds }}')
select
     in_from_code
    ,update_date
    ,in_from_provider_code
    ,in_from_city_code
    ,in_from_area_code
    ,out_to_provider_code
    ,out_to_city_code
    ,out_to_area_code
    ,out_to_code
    ,in_branch_id
    ,main_id
    ,out_branch_id
    ,in_collect_code
    ,in_to_code
    ,start_center_code
    ,search_type
    ,start_network_code
    ,in_from_regional_code
    ,in_from_regional_desc
    ,in_from_financial_center_code
    ,in_from_financial_center_desc
    ,in_from_provider_desc
    ,in_from_city_desc
    ,in_from_area_desc
    ,in_collect_name
    ,in_from_name
    ,in_to_name
    ,in_edge_latest_warehousing
    ,in_edge_planned_departure
    ,in_edge_planned_arrival
    ,in_edge2_planned_departure
    ,in_edge2_planned_arrival
    ,branch_in_span_days
    ,branch_in_total_time
    ,is_main_route
    ,transfer_type
    ,num_of_transfer
    ,whole_route
    ,e1_center_flow
    ,e1_line_code
    ,e1_planned_departure
    ,e1_runtime
    ,e1_planned_arrival
    ,e1_e2_stop_time
    ,e2_center_flow
    ,e2_line_code
    ,e2_planned_departure
    ,e2_runtime
    ,e2_planned_arrival
    ,e2_e3_stop_time
    ,e3_center_flow
    ,e3_line_code
    ,e3_planned_departure
    ,e3_runtime
    ,e3_planned_arrival
    ,e3_e4_stop_time
    ,e4_center_flow
    ,e4_line_code
    ,e4_planned_departure
    ,e4_runtime
    ,e4_planned_arrival
    ,e4_e5_stop_time
    ,e5_center_flow
    ,e5_line_code
    ,e5_planned_departure
    ,e5_runtime
    ,e5_planned_arrival
    ,e5_e6_stop_time
    ,e6_center_flow
    ,e6_line_code
    ,e6_planned_departure
    ,e6_runtime
    ,e6_planned_arrival
    ,e6_e7_stop_time
    ,e1_end_code
    ,e2_end_code
    ,e3_end_code
    ,e4_end_code
    ,e5_end_code
    ,e6_end_code
    ,transfer_total_span_days
    ,transfer_total_time_use
    ,main_total_span_days
    ,main_total_time_use
    ,end_center
    ,end_center_code
    ,start_center
    ,out_from_code
    ,out_collect_code
    ,out_to_regional_desc
    ,out_to_regional_code
    ,out_to_financial_center_desc
    ,out_to_financial_center_code
    ,out_to_provider_desc
    ,out_to_city_desc
    ,out_to_area_desc
    ,out_collect_name
    ,out_to_name
    ,out_from_name
    ,out_has_collect
    ,out_edge_planned_departure
    ,out_edge_planned_arrival_time
    ,out_edge2_planned_departure
    ,out_edge2_planned_arrival_time
    ,out_edge2_latest_warehouse_time
    ,out_edge_latest_warehouse_time
    ,out_edge_span_days_sign
    ,out_edge2_span_days_sign
    ,out_edge_deadline_sign_time
    ,out_edge2_deadline_sign_time
    ,branch_out_span_days
    ,branch_out_total_time
    ,effective_date
    ,expiration_date
    ,branch_in_center_stop_time
    ,center_branch_out_stop_time
    ,branch_in_center_span_days
    ,center_branch_out_span_days
    ,is_circuitous
    ,out_edge_span_days_arrive
    ,out_edge2_span_days_arrive
    ,in_nodes
    ,out_nodes
    ,total_days_use
    ,total_time_use
    ,total_days_t
    ,total_nodes
    ,working_days
    ,extra_in_collect_code
    ,extra_in_collect_name
    ,extra_in_line_name
    ,extra_in_latest_warehousing
    ,extra_in_planned_departure
    ,extra_in_planned_arrival
    ,extra_out_collect_code
    ,extra_out_collect_name
    ,extra_out_line_name
    ,extra_out_latest_warehousing
    ,extra_out_planned_departure
    ,extra_out_planned_arrival
    ,in_network_line_name
    ,in_collect_line_name
    ,out_network_line_name
    ,out_collect_line_name
    ,town_plus_time
    ,branch_in_ship_time
    ,branch_in_ship_span_days
    ,branch_out_ship_time
    ,branch_out_ship_span_days
    ,all_line_name
    ,out_town_plus_time
    ,in_edge_run_time
    ,in_edge_span_days
    ,in_edge2_run_time
    ,in_edge2_span_days
    ,in_edge3_run_time
    ,in_edge3_span_days
    ,out_edge_run_time
    ,out_edge_span_days
    ,out_edge2_run_time
    ,out_edge2_span_days
    ,out_edge3_run_time
    ,out_edge3_span_days
    ,e1_span_days
    ,e2_span_days
    ,e3_span_days
    ,e4_span_days
    ,e5_span_days
    ,e6_span_days
    ,delivery_time
    ,route_flow
    ,in_edge2_latest_warehousing
    ,e1_latest_warehousing
    ,e2_latest_warehousing
    ,e3_latest_warehousing
    ,e4_latest_arrival_time
    ,e5_latest_arrival_time
    ,e6_latest_arrival_time
    ,start_taking_shift
    ,end_send_shift
    ,warehouse_end_time
    ,reserve_1
    ,reserve_2
    ,route_contain_main_line
    ,direct_route_type
    ,in_line_day
    ,out_line_day
    ,in_ship_span_days
    ,in_manage_region_code
    ,in_manage_region_name
    ,out_manage_region_code
    ,out_manage_region_name
    ,is_main_city
from city_slowest_route_result_config
union all
select
     in_from_code
    ,update_date
    ,in_from_provider_code
    ,in_from_city_code
    ,in_from_area_code
    ,out_to_provider_code
    ,out_to_city_code
    ,out_to_area_code
    ,out_to_code
    ,in_branch_id
    ,main_id
    ,out_branch_id
    ,in_collect_code
    ,in_to_code
    ,start_center_code
    ,search_type
    ,start_network_code
    ,in_from_regional_code
    ,in_from_regional_desc
    ,in_from_financial_center_code
    ,in_from_financial_center_desc
    ,in_from_provider_desc
    ,in_from_city_desc
    ,in_from_area_desc
    ,in_collect_name
    ,in_from_name
    ,in_to_name
    ,in_edge_latest_warehousing
    ,in_edge_planned_departure
    ,in_edge_planned_arrival
    ,in_edge2_planned_departure
    ,in_edge2_planned_arrival
    ,branch_in_span_days
    ,branch_in_total_time
    ,is_main_route
    ,transfer_type
    ,num_of_transfer
    ,whole_route
    ,e1_center_flow
    ,e1_line_code
    ,e1_planned_departure
    ,e1_runtime
    ,e1_planned_arrival
    ,e1_e2_stop_time
    ,e2_center_flow
    ,e2_line_code
    ,e2_planned_departure
    ,e2_runtime
    ,e2_planned_arrival
    ,e2_e3_stop_time
    ,e3_center_flow
    ,e3_line_code
    ,e3_planned_departure
    ,e3_runtime
    ,e3_planned_arrival
    ,e3_e4_stop_time
    ,e4_center_flow
    ,e4_line_code
    ,e4_planned_departure
    ,e4_runtime
    ,e4_planned_arrival
    ,e4_e5_stop_time
    ,e5_center_flow
    ,e5_line_code
    ,e5_planned_departure
    ,e5_runtime
    ,e5_planned_arrival
    ,e5_e6_stop_time
    ,e6_center_flow
    ,e6_line_code
    ,e6_planned_departure
    ,e6_runtime
    ,e6_planned_arrival
    ,e6_e7_stop_time
    ,e1_end_code
    ,e2_end_code
    ,e3_end_code
    ,e4_end_code
    ,e5_end_code
    ,e6_end_code
    ,transfer_total_span_days
    ,transfer_total_time_use
    ,main_total_span_days
    ,main_total_time_use
    ,end_center
    ,end_center_code
    ,start_center
    ,out_from_code
    ,out_collect_code
    ,out_to_regional_desc
    ,out_to_regional_code
    ,out_to_financial_center_desc
    ,out_to_financial_center_code
    ,out_to_provider_desc
    ,out_to_city_desc
    ,out_to_area_desc
    ,out_collect_name
    ,out_to_name
    ,out_from_name
    ,out_has_collect
    ,out_edge_planned_departure
    ,out_edge_planned_arrival_time
    ,out_edge2_planned_departure
    ,out_edge2_planned_arrival_time
    ,out_edge2_latest_warehouse_time
    ,out_edge_latest_warehouse_time
    ,out_edge_span_days_sign
    ,out_edge2_span_days_sign
    ,out_edge_deadline_sign_time
    ,out_edge2_deadline_sign_time
    ,branch_out_span_days
    ,branch_out_total_time
    ,effective_date
    ,expiration_date
    ,branch_in_center_stop_time
    ,center_branch_out_stop_time
    ,branch_in_center_span_days
    ,center_branch_out_span_days
    ,is_circuitous
    ,out_edge_span_days_arrive
    ,out_edge2_span_days_arrive
    ,in_nodes
    ,out_nodes
    ,total_days_use
    ,total_time_use
    ,total_days_t
    ,total_nodes
    ,working_days
    ,extra_in_collect_code
    ,extra_in_collect_name
    ,extra_in_line_name
    ,extra_in_latest_warehousing
    ,extra_in_planned_departure
    ,extra_in_planned_arrival
    ,extra_out_collect_code
    ,extra_out_collect_name
    ,extra_out_line_name
    ,extra_out_latest_warehousing
    ,extra_out_planned_departure
    ,extra_out_planned_arrival
    ,in_network_line_name
    ,in_collect_line_name
    ,out_network_line_name
    ,out_collect_line_name
    ,town_plus_time
    ,branch_in_ship_time
    ,branch_in_ship_span_days
    ,branch_out_ship_time
    ,branch_out_ship_span_days
    ,all_line_name
    ,out_town_plus_time
    ,in_edge_run_time
    ,in_edge_span_days
    ,in_edge2_run_time
    ,in_edge2_span_days
    ,in_edge3_run_time
    ,in_edge3_span_days
    ,out_edge_run_time
    ,out_edge_span_days
    ,out_edge2_run_time
    ,out_edge2_span_days
    ,out_edge3_run_time
    ,out_edge3_span_days
    ,e1_span_days
    ,e2_span_days
    ,e3_span_days
    ,e4_span_days
    ,e5_span_days
    ,e6_span_days
    ,delivery_time
    ,route_flow
    ,in_edge2_latest_warehousing
    ,e1_latest_warehousing
    ,e2_latest_warehousing
    ,e3_latest_warehousing
    ,e4_latest_arrival_time
    ,e5_latest_arrival_time
    ,e6_latest_arrival_time
    ,start_taking_shift
    ,end_send_shift
    ,warehouse_end_time
    ,reserve_1
    ,reserve_2
    ,route_contain_main_line
    ,direct_route_type
    ,in_line_day
    ,out_line_day
    ,in_ship_span_days
    ,in_manage_region_code
    ,in_manage_region_name
    ,out_manage_region_code
    ,out_manage_region_name
    ,is_main_city
from city_slowest_route_result_tmp
where config_area_id_start is null and config_area_id_end is null
distribute by pmod(hash(rand()),50);



